<?php

namespace DicManagerBundle\Controller;

use Symfony\Bundle\FrameworkBundle\Controller\Controller;
use Sensio\Bundle\FrameworkExtraBundle\Configuration\Route;
use Symfony\Component\HttpFoundation\Request;
use Symfony\Component\HttpFoundation\Response;

/**
 * Class DicManagerController
 * @package DicManagerBundle\Controller
 * @author chenyl
 */

class DicManagerController extends Controller{
    /**
     * 转到数据字典页面
     * @Route("/dic_bundle/dic_list",name="dic_list")
     */
    public function dicListAction(Request $req){
        //设置page与size的默认值，获取page与size
        $page = $req->get('page')?:1;
        $size = $req->get('size')?:5;
        $offset = ($page-1)*$size;//查询开始位置，0为开始
        //连接到pgsql数据库,需要换成自己的
        $conn = pg_connect('host=192.168.146.128 port=5432 dbname=YiXun user=abssrtemp password=abscd123');
        //查询数据库所有用户表表名与注释
        $sql = "select relname as tablename,
                cast(obj_description(relfilenode,'pg_class') as varchar) as comment 
                from pg_class c where  relkind = 'r' 
                and relname not like 'pg_%' 
                and relname not like 'sql_%' 
                order by relname
                LIMIT $size OFFSET $offset";
        $result = pg_query($conn,$sql);
        $arrdic = array();
        //获取数据到数组,表名与注释
        while($row = pg_fetch_row($result)){
            $arrdic[] = array('table_name'=>$row[0],'comment'=>$row[1]);
        }
        //获取有多少条数据
        $list_len = 0;
        $resultSet = pg_query($conn,"SELECT tablename FROM pg_tables WHERE tablename NOT LIKE 'pg%' AND tablename NOT LIKE 'sql%'");
        while(pg_fetch_row($resultSet)){$list_len++;}
        //获取EntNewsRepository
        return $this->render('DicManagerBundle:Default:index.html.twig',
            array('arrdic'=>$arrdic,
                    'page'=>$page,
                    'per'=>$size,
                    'list_len'=>$list_len));
    }
    /**
     * 添加|修改注释 表|字段
     * for ajax
     * @Route("/dic_bundle/comment",name="dic_comment")
     */
    public function setCommentAction(Request $req){
        $target = $req->get('target');
        $comment = $req->get('comment');
        //获取pgsql连接
        $conn = pg_connect('host=192.168.198.129 port=5432 dbname=YiXun user=postgres password=123456');
        //实现comment的更改|添加
        $sql = "comment on table $target is '$comment'";
        pg_query($conn,$sql);
        return new Response($comment);
    }


    /**
     * 转到数据字典的字段页面,并分页
     * @Route("/dic_bundle/dic_field",name="dic_field")
     */
    public function dicFieldAction(Request $req){
        //设置page与size的默认值，获取page与size
        $page = $req->get('page')?:1;
        $size = $req->get('size')?:5;
        $offset = ($page-1)*$size;//查询开始位置，0为开始
        $table_name = $req->get('table_name');//获取要查询的表名

        //连接到pgsql数据库
        $conn = pg_connect('host=192.168.198.129 port=5432 dbname=YiXun user=postgres password=123456');
        //查询数据库该表的所有字段|分页
        $sql = "SELECT column_name FROM information_schema.columns 
                WHERE table_schema = 'public' 
                AND table_name = '$table_name'
                LIMIT $size OFFSET $offset";
        $result = pg_query($conn,$sql);
        $fieldArr = array();
        while($row = pg_fetch_row($result)){
            $fieldArr[] = $row[0];
        }
        //获取有多少条数据
        $list_len = 0;
        $resultSet = pg_query($conn,"SELECT column_name FROM information_schema.columns WHERE table_schema = 'public' AND table_name='$table_name'");
        while(pg_fetch_row($resultSet)){$list_len++;}
        return $this->render('DicManagerBundle:Default:field.html.twig',
            array('fieldArr'=>$fieldArr,
                    'page'=>$page,
                    'per'=>$size,
                    'list_len'=>$list_len,
                    'table_name'=>$table_name
            ));
    }

//    /**
//     * 添加数据字典与修改
//     * @Route("/dic_bundle/add_data_dic",name="add_data_dic")
//     */
//    public function addDataDicAction(Request $req){
//        $id = $req->get('id');
//        if(null==$id){
//            //获取服务对象
//            $common_service = $this->get('common_service');
//            //获取entity_manager
//            $em = $this->getDoctrine()->getManager();
//
//            $table_name = $req->get('table_name');//表名
//            $field_name = $req->get('field_name');//字段名
//            $name = $req->get('name');//数据类
//            $ent_data_dict = new EntDataDict();//数据字典对象
//            //设置对象属性
//            $ent_data_dict->setFieldName($field_name);
//            $ent_data_dict->setName($name);
//            $ent_data_dict->setTableName($table_name);
//            //储存数据字典
//            $em->persist($ent_data_dict);
//            $em->flush();
//            //重定向返回页面
//            return $this->redirectToRoute('paginator',array('name'=>'system-data','bundle'=>'CtmsBundle','class'=>'EntDataDict'));
//
//        }else{
//            $name = $req->get('name');
//            $data_obj = $em->getRepository('CtmsBundle:EntDataDict')->find($id);
//            $data_obj->setName($name);
//            $em->flush();
//            return $this->redirectToRoute('jump',array('name'=>'determine'));
//        }
//
//    }
//
//    /**
//     * 从数据字典删除
//     * @Route("/dic_bundle/delete_data_dic",name="delete_data_dic")
//     */
//    public function deleteDataDicAction(Request $req){
//        $id = $req->get('id');
//        $em = $this->getDoctrine()->getManager();
//        $data_obj = $em->getRepository('CtmsBundle:EntDataDict')->find($id);
//
//        $em->remove($data_obj);
//        $em->flush();
//
//        return $this->redirectToRoute('paginator',array('name'=>'system-data','bundle'=>'CtmsBundle','class'=>'EntDataDict'));
//    }
//
//    /**
//     * ajax级联查询
//     * @Route("/dic_bundle/relation_query",name="relation_query")
//     */
//    public function relationQueryAction(Request $req){
//        $tableName = $req->get('table_name');
//        $conn = 'host=192.168.198.129 port=5432 dbname=abssrtemp user=postgres password=123456';
//        $pg = pg_connect($conn);
//        $sql = "select column_name from information_schema.columns where table_schema= 'public' and table_name='".$tableName."'";
//        $result = pg_query($pg,$sql);
//        $responseText = '';
//        //遍历result集合，得到要返回的字符串
//        while($row = pg_fetch_row($result)){
//            $responseText.='<option value="'.$row[0].'">'.$row[0].'</option>';
//        }
//        return new Response($responseText);
//    }
}